****************************************************************************
**Win or Lose: Residential Sorting After a School Choice Lottery
*By Andrew Bibler & Stephen B Billings

****************************************************************************

clear all 
set more off

foreach year in 09 10 11 12 13 14 15 { 

use mb_20`year'_pub.dta , clear 

gen year = 20`year' 

keep if mastid!=. 

keep if (grade=="03" | grade=="04" | grade=="05" | grade=="06" | grade=="07" | grade=="08") 
	
gen grade_r = real(grade) 
drop grade 
rename grade_r grade 
	
if (`year' == 10 | `year' == 11 | `year' == 12) { 
	rename ma_test_dt ma_test_date 
	rename rd_test_dt rd_test_date 
	rename ma_scoren ma_score 
	rename rd_scoren rd_score 
	} 

if (`year' == 13 | `year' == 14 | `year'==15) { 
	rename pc_ma_score ma_score 
	rename pc_rd_score rd_score 
	rename pc_ma_date ma_test_date 
	rename pc_rd_date rd_test_date 
	
	rename lep_year_exited lep_exit_year 
	} 

cap noisily gen ma_score_r = real(ma_score) 
cap noisily gen ma_score_r = ma_score 

cap noisily gen rd_score_r = real(rd_score) 
cap noisily gen rd_score_r = rd_score 

drop ma_score rd_score 

rename ma_score_r ma_score  
rename rd_score_r rd_score 

duplicates tag mastid grade , gen(dups) 

if (year!=2013 & year!=2014 & year!=2015) { 
gen ma_valid = (regexm(ma_test_id, ".*MA") & (grade>=3) & (grade<=8)) 
gen rd_valid = (regexm(rd_test_id, ".*RD") & (grade>=3) & (grade<=8)) 
gen both_valid = (ma_valid==1 & rd_valid==1) 
	} 
	
if (year==2013 | year==2014 | year==2015) { 
gen ma_valid = regexm(pc_ma_type, ".*RG") 
gen rd_valid = regexm(pc_rd_type, ".*RG") 
gen both_valid = (ma_valid==1 & rd_valid==1) 
	} 
	
bysort mastid grade: egen num_valid = count(both_valid) 

drop if (both_valid==0 & num_valid>=1) 

duplicates tag mastid grade , gen(dups2) 

replace ma_score = -1 if (ma_score==. | ma_valid==0) 
replace rd_score = -1 if (rd_score==. | rd_valid==0) 
bysort mastid: egen mahigh = max(ma_score) if dups2>0
bysort mastid: egen rdhigh = max(rd_score) if dups2>0 


gen math = . 
replace math = ma_score if dups2==0 
replace math = mahigh if dups2>=1 

gen read = . 
replace read = rd_score if dups2==0 
replace read = rdhigh if dups2>=1 

duplicates drop mastid grade math read , force 

duplicates report mastid grade 

replace read = . if read==-1
replace math = . if math==-1 

gen mathstand=. 
gen readstand=. 

if year!=2013 & year!=2014 & year!=2015 { 
	
su math if ma_test_id=="MA03" 
scalar ma_mn3 = r(mean) 
scalar ma_sd3 = r(sd) 

su math if ma_test_id=="MA04" 
scalar ma_mn4 = r(mean) 
scalar ma_sd4 = r(sd) 

su math if ma_test_id=="MA05" 
scalar ma_mn5 = r(mean) 
scalar ma_sd5 = r(sd) 

su math if ma_test_id=="MA06" 
scalar ma_mn6 = r(mean) 
scalar ma_sd6 = r(sd) 

su math if ma_test_id=="MA07" 
scalar ma_mn7 = r(mean) 
scalar ma_sd7 = r(sd) 

su math if ma_test_id=="MA08" 
scalar ma_mn8 = r(mean) 
scalar ma_sd8 = r(sd) 

su read if rd_test_id=="RD03" 
scalar rd_mn3 = r(mean) 
scalar rd_sd3 = r(sd) 

su read if rd_test_id=="RD04" 
scalar rd_mn4 = r(mean) 
scalar rd_sd4 = r(sd) 

su read if rd_test_id=="RD05" 
scalar rd_mn5 = r(mean) 
scalar rd_sd5 = r(sd) 

su read if rd_test_id=="RD06" 
scalar rd_mn6 = r(mean) 
scalar rd_sd6 = r(sd) 

su read if rd_test_id=="RD07" 
scalar rd_mn7 = r(mean) 
scalar rd_sd7 = r(sd) 

su read if rd_test_id=="RD08" 
scalar rd_mn8 = r(mean) 
scalar rd_sd8 = r(sd) 

replace mathstand = ((math - ma_mn3)/ma_sd3) if ma_test_id=="MA03" 
replace mathstand = ((math - ma_mn4)/ma_sd4) if ma_test_id=="MA04" 
replace mathstand = ((math - ma_mn5)/ma_sd5) if ma_test_id=="MA05" 
replace mathstand = ((math - ma_mn6)/ma_sd6) if ma_test_id=="MA06" 
replace mathstand = ((math - ma_mn7)/ma_sd7) if ma_test_id=="MA07" 
replace mathstand = ((math - ma_mn8)/ma_sd8) if ma_test_id=="MA08" 

replace readstand = ((read - rd_mn3)/rd_sd3) if rd_test_id=="RD03" 
replace readstand = ((read - rd_mn4)/rd_sd4) if rd_test_id=="RD04" 
replace readstand = ((read - rd_mn5)/rd_sd5) if rd_test_id=="RD05" 
replace readstand = ((read - rd_mn6)/rd_sd6) if rd_test_id=="RD06" 
replace readstand = ((read - rd_mn7)/rd_sd7) if rd_test_id=="RD07" 
replace readstand = ((read - rd_mn8)/rd_sd8) if rd_test_id=="RD08" 

	} 

	
if (year==2013 | year==2014 | year==2015) { 
	
su math if (grade==3) 
scalar ma_mn3 = r(mean) 
scalar ma_sd3 = r(sd) 

su math if (grade==4) 
scalar ma_mn4 = r(mean) 
scalar ma_sd4 = r(sd) 

su math if (grade==5) 
scalar ma_mn5 = r(mean) 
scalar ma_sd5 = r(sd) 

su math if (grade==6) 
scalar ma_mn6 = r(mean) 
scalar ma_sd6 = r(sd) 

su math if (grade==7) 
scalar ma_mn7 = r(mean) 
scalar ma_sd7 = r(sd) 

su math if (grade==8) 
scalar ma_mn8 = r(mean) 
scalar ma_sd8 = r(sd) 

su read if (grade==3) 
scalar rd_mn3 = r(mean) 
scalar rd_sd3 = r(sd) 

su read if (grade==4) 
scalar rd_mn4 = r(mean) 
scalar rd_sd4 = r(sd) 

su read if (grade==5)
scalar rd_mn5 = r(mean) 
scalar rd_sd5 = r(sd) 

su read if (grade==6)
scalar rd_mn6 = r(mean) 
scalar rd_sd6 = r(sd) 

su read if (grade==7)
scalar rd_mn7 = r(mean) 
scalar rd_sd7 = r(sd) 

su read if (grade==8)
scalar rd_mn8 = r(mean) 
scalar rd_sd8 = r(sd) 

replace mathstand = ((math - ma_mn3)/ma_sd3) if (grade==3) 
replace mathstand = ((math - ma_mn4)/ma_sd4) if (grade==4) 
replace mathstand = ((math - ma_mn5)/ma_sd5) if (grade==5) 
replace mathstand = ((math - ma_mn6)/ma_sd6) if (grade==6) 
replace mathstand = ((math - ma_mn7)/ma_sd7) if (grade==7) 
replace mathstand = ((math - ma_mn8)/ma_sd8) if (grade==8) 

replace readstand = ((read - rd_mn3)/rd_sd3) if (grade==3) 
replace readstand = ((read - rd_mn4)/rd_sd4) if (grade==4) 
replace readstand = ((read - rd_mn5)/rd_sd5) if (grade==5) 
replace readstand = ((read - rd_mn6)/rd_sd6) if (grade==6) 
replace readstand = ((read - rd_mn7)/rd_sd7) if (grade==7) 
replace readstand = ((read - rd_mn8)/rd_sd8) if (grade==8) 

	} 
	

bysort grade: su mathstand readstand 

gen cms = lea == "600" 

gen charter = schlcode == "000"					

gen frl_mb = . 
replace frl_mb = 1 if (eds == "Y") 
replace frl_mb = 0 if (eds == "N") 

gen exceptionality = . 

	replace exceptionality = 0 if ec_code == "NULL" 
	replace exceptionality = 1 if ec_code == "AU  " 
	replace exceptionality = 2 if ec_code == "DB  " 
	replace exceptionality = 3 if ec_code == "DD  " 
	replace exceptionality = 4 if ec_code == "DF  " 
	replace exceptionality = 5 if ec_code == "ED  " 
	replace exceptionality = 6 if ec_code == "HI  " 
	replace exceptionality = 7 if ec_code == "IDMI" 
	replace exceptionality = 8 if ec_code == "IDMO" 
	replace exceptionality = 9 if ec_code == "IDSE" 
	replace exceptionality = 10 if ec_code == "LD  " 
	replace exceptionality = 11 if ec_code == "MU  " 
	replace exceptionality = 12 if ec_code == "OH  " 
	replace exceptionality = 13 if ec_code == "OI  " 
	replace exceptionality = 14 if ec_code == "SI  " 
	replace exceptionality = 15 if ec_code == "TB  " 
	replace exceptionality = 16 if ec_code == "VI  " 
	
if `year'==2014 { 

	replace exceptionality = 17 if ec_code == "ATD " 
	replace exceptionality = 18 if ec_code == "EST " 
	replace exceptionality = 19 if ec_code == "HRE " 
	replace exceptionality = 20 if ec_code == "HRP " 
		
	} 

if (`year'==09 | `year'==10) {    
   gen white_mb = ethnicity=="WHTE" 
   gen black_mb = ethnicity=="BLCK" 
   gen hispanic_mb = ethnicity=="HISP" 
   } 
   
if (`year'==11 | `year'==12) { 
   gen white_mb = ethnicity=="W" 
   gen black_mb = ethnicity=="B" 
   gen hispanic_mb = ethnicity=="H" 
	} 
   
if (`year'==13 | `year'==14 | `year'==15) { 
   gen white_mb = ethnic=="W" 
   gen black_mb = ethnic=="B" 
   gen hispanic_mb = ethnic=="H" 
	} 
   	
gen ma_aig = 0 
gen rd_aig = 0 

if (`year' == 09 | `year' == 10 | `year' == 11 | `year' == 12 ) { 
	replace ma_aig = 1 if aig_m == "Y"  
	replace ma_aig = . if (aig_m != "Y" & aig_m != "N") 
	
	replace rd_aig = 1 if aig_r == "Y"  
	replace rd_aig = . if (aig_r != "Y" & aig_r != "N") 
	} 

if (`year' == 13 | `year' == 14 | `year'==15 ) { 
	replace ma_aig = 1 if ( aig == "B" | aig == "M" ) 
	replace ma_aig = . if ( aig != "B" & aig != "M" & aig != "N" & aig != "R" ) 
	
	replace rd_aig = 1 if ( aig == "B" | aig == "R" ) 
	replace rd_aig = . if ( aig != "B" & aig != "M" & aig != "N" & aig != "R" ) 
	} 
	
gen lep_Y = lep=="Y"
gen lep_U = lep=="U" 


keep mastid grade sex ma_aig rd_aig mathstand readstand ///
		year grade cms charter schlcode lea frl_mb ///
		lep_Y lep_U exceptionality white_mb hispanic_mb black_mb 
			
tempfile mb`year' 
	save `mb`year'' , replace 
		
		} 
	
use `mb09' , clear 

foreach year in 10 11 12 13 14 15 { 
	
	append using `mb`year'' 
		
	} 
	
	
duplicates tag mastid grade , gen(repeat) 

bysort mastid grade: egen firstyear = min(year) 
drop if (repeat>0 & year!=firstyear) 

drop firstyear 
drop repeat 

rename year mbyear 
rename grade testgrade 

tempfile mbclean
	save `mbclean'

save MB_Clean_Long.dta , replace 




use MB_Clean_Long.dta , clear

keep mastid testgrade frl_mb

keep if testgrade==3 

rename frl_mb frl_mb3 

keep mastid frl_mb3

duplicates drop mastid , force

save frl_mb3.dta , replace
	
	

use MB_Clean_Long.dta, clear 

keep if lea=="600"

gen lea_r=real(lea)
drop lea
rename lea_r lea

gen schlcode_r = real(schlcode) 
drop schlcode 
rename schlcode_r schlcode

levelsof schlcode , local(schslocal)

levelsof schlcode if testgrade==3 , local(elemschslocal)

levelsof schlcode if testgrade==6 , local(midschslocal) 

xtset mastid testgrade
gen math_lag = L.mathstand
gen read_lag = L.readstand

foreach s of local elemschslocal {

gen elemsch`s' = (schlcode==`s') 

}

foreach s of local midschslocal {

gen midsch`s' = (schlcode==`s') 

}

gen female = regexm(sex , ".*F") 


** VA
gen schoolva_ma = .

reg mathstand math_lag female white_mb black_mb hispanic_mb frl_mb i.exceptionality ma_aig rd_aig lep_Y lep_U i.testgrade i.mbyear elemsch* if inlist(testgrade , 4 , 5) 

foreach s of local elemschslocal {

replace schoolva_ma = _b[elemsch`s'] if schlcode==`s'

} 

reg mathstand math_lag female white_mb black_mb hispanic_mb frl_mb i.exceptionality ma_aig rd_aig lep_Y lep_U i.testgrade i.mbyear midsch* if inlist(testgrade , 6 , 7, 8) 

foreach s of local midschslocal {

replace schoolva_ma = _b[midsch`s'] if schlcode==`s'

} 

gen schoolva_rd = .

reg readstand read_lag female white_mb black_mb hispanic_mb frl_mb i.exceptionality ma_aig rd_aig lep_Y lep_U i.testgrade i.mbyear elemsch* if inlist(testgrade , 4 , 5) 

foreach s of local elemschslocal {

replace schoolva_rd = _b[elemsch`s'] if schlcode==`s'

} 

reg readstand read_lag female white_mb black_mb hispanic_mb frl_mb i.exceptionality ma_aig rd_aig lep_Y lep_U i.testgrade i.mbyear midsch* if inlist(testgrade , 6 , 7, 8) 

foreach s of local midschslocal {

replace schoolva_rd = _b[midsch`s'] if schlcode==`s'

} 

gen schoolva = 0.5*schoolva_ma + 0.5*schoolva_rd

gen aig = (ma_aig==1 | rd_aig==1)

keep mathstand readstand schoolva schlcode lea frl_mb white_mb aig lep_Y

collapse (mean) mathstand readstand frl_mb white_mb aig lep_Y schoolva (sd) mathstand_sd=mathstand readstand_sd=readstand , by(lea schlcode) 

gen elemschl=0

foreach x of local elemschslocal {
replace elemschl=1 if schlcode==`x'
}

gen midschl=0

foreach x of local midschslocal {
replace midschl=1 if schlcode==`x'
}

save schlcode_avgchars_cms.dta , replace
	
rename schlcode assschl 

foreach v in mathstand readstand frl_mb white_mb aig lep_Y schoolva {

rename `v' `v'_as

	}
	
keep assschl mathstand_as readstand_as frl_mb_as white_mb_as aig_as lep_Y_as schoolva_as

save scores-assigned.dta, replace 

rename assschl curr_lead1

foreach v in mathstand readstand frl_mb white_mb aig lep_Y schoolva {

rename `v'_as `v'_at

	}
	
save scores-attended.dta, replace


	
	
	
	
	
	
